BEGIN;
CREATE TABLE `pp_case_ppcaseinfo` (
    `caseID` varchar(20) NOT NULL PRIMARY KEY,
    `orderNo` varchar(25),
    `paymentAmount` numeric(9, 2) NOT NULL,
    `paymentCurrency` varchar(3) NOT NULL,
    `transactionID` varchar(17) NOT NULL,
    `caseStartDate` date NOT NULL,
    `paypalCaseDeclaration` varchar(20),
    `paypalCaseStatus` varchar(8),
    `paypalCaseWindow` integer
)
;
CREATE TABLE `pp_case_ppcaseinvestigation` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `orderNo_id` varchar(20) NOT NULL,
    `departmentGroup` varchar(10),
    `investigationInfo` varchar(256),
    `reasonGroup` varchar(25),
    `involvedProvider` varchar(25),
    `involvedCategory` varchar(25)
)
;
ALTER TABLE `pp_case_ppcaseinvestigation` ADD CONSTRAINT `orderNo_id_refs_caseID_690184c` FOREIGN KEY (`orderNo_id`) REFERENCES `pp_case_ppcaseinfo` (`caseID`);
CREATE TABLE `pp_case_ppcasehandle` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `caseID_id` integer NOT NULL,
    `handleDate` date NOT NULL,
    `handleAction` varchar(20) NOT NULL
)
;
ALTER TABLE `pp_case_ppcasehandle` ADD CONSTRAINT `caseID_id_refs_id_6aaf18fe` FOREIGN KEY (`caseID_id`) REFERENCES `pp_case_ppcaseinvestigation` (`id`);
CREATE TABLE `pp_case_ppcaserefund` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `orderNo_id` varchar(20) NOT NULL,
    `refundDate` date NOT NULL,
    `refundAmount` numeric(9, 2) NOT NULL
)
;
ALTER TABLE `pp_case_ppcaserefund` ADD CONSTRAINT `orderNo_id_refs_caseID_750e862b` FOREIGN KEY (`orderNo_id`) REFERENCES `pp_case_ppcaseinfo` (`caseID`);
CREATE INDEX `pp_case_ppcaseinvestigation_13e8098a` ON `pp_case_ppcaseinvestigation` (`orderNo_id`);
CREATE INDEX `pp_case_ppcasehandle_2cd4a15f` ON `pp_case_ppcasehandle` (`caseID_id`);
CREATE INDEX `pp_case_ppcaserefund_13e8098a` ON `pp_case_ppcaserefund` (`orderNo_id`);
COMMIT;
